/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models;

import helpers.Utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

/**
 *
 * @author Rasztemberg
 */
public class Hobby implements DataModel {
    
    private int id;
    private String name;
    
    public final static String TABLE_NAME = "hobbies";
    public final static String RELATIONSHIP_TABLE_NAME = "user_has_hobbies";
    public final static String RELATIONSHIP_USER_ID_FIELD_NAME = "user_id";
    public final static String RELATIONSHIP_HOBBY_ID_FIELD_NAME = "hobbies_id";
    public final static String ID_FIELD_NAME = "id";
    public final static String HOBBY_FIELD_NAME = "name";
    
    
    private static Logger logger = LogManager.getLogger(Hobby.class.getName());
    
    /* ACTIONS -----------------------------------------------------------------*/
    
    @Override
    public int insert() {
       
        Connection connection = null; 
        int result = 0;
        
        try {
            connection = Utils.getConnection();
            
            StringBuilder query = new StringBuilder();
            query.append("INSERT INTO " + TABLE_NAME); 
            query.append("("+ HOBBY_FIELD_NAME +")");
            query.append("VALUES (?)");
            
            PreparedStatement ps = connection.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, name);
            
            result = ps.executeUpdate();
            ResultSet rs = ps.getGeneratedKeys();
            
            if (result >= 1) {
                //get the ID of the new inserted user
                if (rs.next()) {
                    this.id = rs.getInt(1);
               }
            }
            rs.close();
        } catch (SQLException ex) {
            logger.error("Error when adding a new hobby", ex);
        } finally{
            try {
                if (connection != null && !connection.isClosed()){
                    connection.close();
                }
            } catch (SQLException ex) {
               logger.error("Error closing connection", ex);
            }            
        }       
        return result;
    }

    @Override
    public DataModel selectById(long id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

        
    @Override
    public int update() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int delete() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<DataModel> selectByAttribute(String name, String value) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    
    
    /* RELATIONSHIPS ---------------------------------------------------------------*/
    public static List <Hobby> selectUserHobbies(int userId){
        
        Connection connection = null;
        List<Hobby> hobbies = null;
        
        try {
            connection = Utils.getConnection();
            StringBuilder query = new StringBuilder();
       
            query.append("SELECT " );
            query.append("h.");
            query.append(Hobby.HOBBY_FIELD_NAME);
            query.append(" FROM ");
            query.append(Hobby.TABLE_NAME);
            query.append(" h ,");
            query.append(RELATIONSHIP_TABLE_NAME);
            query.append(" r WHERE r.");
            query.append(RELATIONSHIP_USER_ID_FIELD_NAME);
            query.append( " = ? AND r.");
            query.append(RELATIONSHIP_HOBBY_ID_FIELD_NAME);
            query.append(" = h.");
            query.append(Hobby.ID_FIELD_NAME);
            
            PreparedStatement ps = connection.prepareStatement(query.toString());
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            
            hobbies = new ArrayList<Hobby>();
            while(rs.next()){
                Hobby h = new Hobby();
                h.setName(rs.getString(1)); // Hobby column table
                hobbies.add(h);
            }
            
         } catch (SQLException ex) {
             logger.error("SQLException when selecting user hobbies for User "+userId, ex);
        }finally{
            try {
                if (connection != null && !connection.isClosed()){
                    connection.close();
                }
            } catch (SQLException ex) {
               logger.error("Error closing connection", ex);
            }            
        }       
        return hobbies;
    }
    
    public static List <Hobby> selectAllHobbies(){
        
        Connection connection = null; 
        List<Hobby> hobbies = null;
        try {
            
            connection = Utils.getConnection();
            StringBuilder query = new StringBuilder(); 
            query.append("SELECT * FROM " + TABLE_NAME);
            
            PreparedStatement ps = connection.prepareStatement(query.toString());
            ResultSet rs = ps.executeQuery();
            
            if (!rs.next()) {
                return null;
            }      
            hobbies = new ArrayList<Hobby>();
            while(rs.next()){
                Hobby h = new Hobby();
                h.setId(rs.getInt(ID_FIELD_NAME));
                h.setName(rs.getString(HOBBY_FIELD_NAME));
                hobbies.add(h);
            }
            
        } catch (SQLException ex) {
            logger.error("SQLException when selecting all hobbies", ex);
        }finally{
            try {
                if (connection != null && !connection.isClosed()){
                    connection.close();
                }
            } catch (SQLException ex) {
               logger.error("Error closing connection ", ex);
            }            
        }
      
        return hobbies;
        
    }
    
    
    public static int addUserHobby(int user_id, int hobby_id){
        
        Connection connection = null; 
        int res = -1;
        
        try { 
            connection = Utils.getConnection();
            StringBuilder query = new StringBuilder(); 
            query.append("INSERT INTO "); 
            query.append(RELATIONSHIP_TABLE_NAME); 
            query.append(" VALUES(?,?)");
            
            PreparedStatement ps = connection.prepareStatement(query.toString());
            
            ps.setInt(1, user_id);
            ps.setInt(2, hobby_id);
            
            res = ps.executeUpdate();                        
            if (res >=1 ){
                logger.info("Added hobby "+hobby_id+" to user "+user_id, null);
            }
        } catch (SQLException ex) {
            logger.error("Error adding a new Hobby ", ex);
        } finally{
            try {
                if (connection != null && !connection.isClosed()){
                    connection.close();
                }
            } catch (SQLException ex) {
               logger.error("Error closing connection", ex);
            }            
        }
        
        return res;
    }
    
    
    /* SETTERS ----------------------------------------------------------------------*/
    
    public void setId(int id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }
    
    
    /* GETTERS ----------------------------------------------------------------------*/

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    
}
